package com.szcinda.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.szcinda.controller.params.CommonExportVo;
import com.szcinda.repository.BankInfo;
import com.szcinda.repository.BankInfoRepository;
import com.szcinda.service.dto.analysis.*;
import com.szcinda.service.dto.log.ExportCountService;
import lombok.Data;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@RequestMapping("orderAnalysis")
@RestController
public class OrderAnalysisController {

    private final OrderAnalysisService orderAnalysisService;
    private final ExportCountService exportCountService;
    static final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    static final DateTimeFormatter dateTimeFormatter2 = DateTimeFormatter.ofPattern("yyyyMM");
    private final BankInfoRepository bankInfoRepository;

    public OrderAnalysisController(OrderAnalysisService orderAnalysisService, ExportCountService exportCountService, BankInfoRepository bankInfoRepository) {
        this.orderAnalysisService = orderAnalysisService;
        this.exportCountService = exportCountService;
        this.bankInfoRepository = bankInfoRepository;
    }

    @PostMapping("/zadOrderQuery")
    public Result<List<ZadOrderAnalysisDto>> zadOrderQuery(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.queryZadOrderAnalysis(params));
    }

    // 国际班列业务分析
    @PostMapping("/zadOrderCount")
    public Result<ZadOrderYearCountDto> zadOrderCount(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.zadOrderCount(params));
    }

    // 业务量分析表
    @PostMapping("getCountAge")
    public Result<List<StartPlaceCountDto>> getCountAge(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.getCountAge(params));
    }

    // 数量盈利分析表
    @PostMapping("/ghOrderYearCount")
    public Result<GhOrderYearCountDto> ghOrderYearCount(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.ghOrderYearCount(params));
    }

    // 项目数据汇总表
    @PostMapping("/ghMoneyCount")
    public Result<List<ProjectCountDto>> ghMoneyCount(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.ghMonthCount(params));
    }

    // 客户线路价格折线图
    @PostMapping("/clientPriceCount")
    public Result<?> clientPriceCount(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.clientPriceCount(params));
    }

    // 供应商线路价格折线图
    @PostMapping("/carrierPriceCount")
    public Result<?> carrierPriceCount(@RequestBody ZadOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.carrierPriceCount(params));
    }

    @PostMapping("/ghOrderQuery")
    public Result<List<GhOrderAnalysisDto>> ghOrderQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.queryGhOrderAnalysis(params));
    }

    @PostMapping("/queryProfit")
    public Result<List<OrderProfitDto>> queryProfit(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.queryProfit(params));
    }

    @PostMapping("/commonExport")
    public void commonExcel(@RequestParam(name = "fileName") String fileName, @RequestParam(name = "headers") String headers, @RequestParam(name = "rows") String rows, HttpServletResponse response) throws IOException {
        /**
         * 通用导出方法
         */
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet1");
        sheet.setDefaultColumnWidth(20);// 默认列宽
        HSSFRow row;
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        HSSFCell cell;
        // 生成标题
        String[] header_row_list = headers.split(";"); //  有多少行表头
        // 取最后一行表头作为标题行
        String[] large_row = header_row_list[header_row_list.length - 1].split(",");
        // 每行表头的最大长度
        int size = large_row.length;
        // 遍历每一行表头，生成单独一行
        int rowNum = 0;
        for (String row_list_str : header_row_list) {
            String[] headerArray = row_list_str.split(",");
            // 创建当前行
            row = sheet.createRow(rowNum);
            for (int i = 0; i < size; i++) {
                cell = row.createCell((short) i);
                cell.setCellValue(headerArray[i]);
                cell.setCellStyle(style);
            }
            // 加一行
            rowNum++;
        }
        //将字符串转换成json数组
        JSONArray jsArr = JSONObject.parseArray(rows);
        int rowNumber = header_row_list.length - 1; // 可能存在多行表头，这样创建row时需要从多行表头的索引加一个开始
        int jsSize = jsArr.size();
        for (int i = 0; i < jsSize; i++) {
            row = sheet.createRow(rowNumber + 1);
            JSONObject js = jsArr.getJSONObject(i);
            for (int j = 0; j < size; j++) {
                cell = row.createCell((short) j);
                cell.setCellValue(js.get(large_row[j]) == null ? "" : js.get(large_row[j]).toString());
                cell.setCellStyle(style);
            }
            rowNumber++;
        }
        // 下载EXCEL
        OutputStream out = response.getOutputStream();
        String fName = URLEncoder.encode(fileName + "-" + LocalDateTime.now().format(dateTimeFormatter2), "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fName + ".xls");
        wb.write(out);
        out.flush();
    }


    @PostMapping("exportProfit")
    public void exportProfit(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, @RequestParam(required = false) String carrierId, @RequestParam(required = false) String clientId, @RequestParam(required = false) String organizationId, @RequestParam(required = false) String userId, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        params.setClientId(clientId);
        params.setCarrierId(carrierId);
        params.setOrganizationId(organizationId);
        List<OrderProfitDto> profitDtos = orderAnalysisService.queryProfit(params);
        InputStream is = new ClassPathResource("templates/template3.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("应收应付报表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        //定义合并单元格策略
        List<Integer> groupCount = new ArrayList<>();
        Map<String, List<OrderProfitDto>> map = profitDtos.stream().filter(dto -> !dto.getClientName().equals("总和")).collect(Collectors.groupingBy(OrderProfitDto::getClientName));
        map.forEach((clientName, list) -> groupCount.add(list.size()));

        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(0, "账务");
        Integer[] array = new Integer[]{0, 1, 2, 3, 4, 5, 13};
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy;
        for (Integer index : array) {
            int startRowIndex = 2;
            for (Integer integer : groupCount) {
                onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(startRowIndex, startRowIndex + integer - 1, index, index);
                builder.registerWriteHandler(onceAbsoluteMergeStrategy);
                startRowIndex = startRowIndex + integer;
            }
        }
        WriteSheet sheet = builder.build();
        write.fill(profitDtos, sheet);
        // 关闭流
        write.finish();
    }

    @Data
    public static class Profix implements Serializable {
        private String clientName;
        private double inTransportFeeOfRMB;
        private double inTransportFeeOfUSD;
        private double inOtherOfRMB;
        private double inOtherOfUSD;
        private double inAmount;
        private double profit;

        public List<Carrier> carriers = new ArrayList<>();

        @Data
        public static class Carrier implements Serializable {
            public String organizationName;
            private String carrierName;
            private double outTransportFeeOfRMB;
            private double outTransportFeeOfUSD;
            private double outOtherOfRMB;
            private double outOtherOfUSD;
            private double outAmount;
        }
    }

    @PostMapping("/cargoQuery")
    public Result<List<CargoDataDto>> cargoQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.cargoQuery(params));
    }

    @PostMapping("cargoExport")
    public void cargoExport(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        List<CargoDataDto> cargoDataDtos = orderAnalysisService.cargoQuery(params);
        InputStream is = new ClassPathResource("templates/cargo.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("货物品类汇总", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(0, "汇总");
        WriteSheet sheet = builder.build();
        write.fill(cargoDataDtos, sheet);
        // 关闭流
        write.finish();
    }


    @PostMapping("/businessQuery")
    public Result<List<BusinessDataDto>> businessQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.businessQuery(params));
    }

    @PostMapping("businessExport")
    public void businessExport(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        List<BusinessDataDto> businessDataDtos = orderAnalysisService.businessQuery(params);
        InputStream is = new ClassPathResource("templates/业务类型汇总.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("业务类型汇总", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(0, "汇总");
        WriteSheet sheet = builder.build();
        write.fill(businessDataDtos, sheet);
        // 关闭流
        write.finish();
    }

    @PostMapping("/lineQuery")
    public Result<List<LineDataDto>> lineQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.lineQuery(params));
    }

    @PostMapping("lineExport")
    public void lineExport(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        List<LineDataDto> lineDataDtos = orderAnalysisService.lineQuery(params);
        InputStream is = new ClassPathResource("templates/铁路业务汇总.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("铁路业务汇总", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(0, "汇总");
        WriteSheet sheet = builder.build();
        write.fill(lineDataDtos, sheet);
        // 关闭流
        write.finish();
    }

    @PostMapping("/userOperation")
    public Result<List<UserOperateDataDto>> userOperation(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.operateQuery(params));
    }

    @PostMapping("userOperationExport")
    public void userOperationExport(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        List<UserOperateDataDto> operateDataDtos = orderAnalysisService.operateQuery(params);
        Map<String, List<UserOperateDataDto>> map = operateDataDtos.stream().collect(Collectors.groupingBy(UserOperateDataDto::getOrganizationName));
        //定义合并单元格策略
        List<Integer> groupCount = new ArrayList<>();
        map.forEach((clientName, list) -> groupCount.add(list.size()));
        InputStream is = new ClassPathResource("templates/操作情况汇总.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("操作情况汇总", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(0, "汇总");
        Integer[] array = new Integer[]{0, 3};
        OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy;
        for (Integer index : array) {
            int startRowIndex = 1;
            for (Integer integer : groupCount) {
                onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(startRowIndex, startRowIndex + integer - 1, index, index);
                builder.registerWriteHandler(onceAbsoluteMergeStrategy);
                startRowIndex = startRowIndex + integer;
            }
        }
        WriteSheet sheet = builder.build();

        write.fill(operateDataDtos, sheet);
        // 关闭流
        write.finish();
    }

    @PostMapping("/clientCountQuery")
    public Result<List<OrderCountDto>> clientCountQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.clientCountQuery(params));
    }

    @PostMapping("/carrierCountQuery")
    public Result<List<OrderCountDto>> carrierOrderQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.carrierOrderQuery(params));
    }

    // exportClient
    @PostMapping("exportClient")
    public void exportClient(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        List<OrderCountDto> orderCountDtos = orderAnalysisService.clientCountQuery(params);
        List<LinePriceDataDto> linePriceDataDtos = orderAnalysisService.clientPriceQuery(params);
        InputStream is = new ClassPathResource("templates/业务量汇总.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("客户业务量汇总", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        WriteSheet sheet1 = EasyExcel.writerSheet(0, "业务量对比").build();
        ;
        WriteSheet sheet2 = EasyExcel.writerSheet(1, "线路对比").build();
        ;
        write.fill(orderCountDtos, sheet1);
        write.fill(linePriceDataDtos, sheet2);
        // 关闭流
        write.finish();
    }

    @PostMapping("/clientPriceQuery")
    public Result<List<LinePriceDataDto>> clientPriceQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.clientPriceQuery(params));
    }

    @PostMapping("/carrierPriceQuery")
    public Result<List<LinePriceDataDto>> carrierPriceQuery(@RequestBody GhOrderAnalysisQueryDto params) {
        return Result.success(orderAnalysisService.carrierPriceQuery(params));
    }

    @PostMapping("exportCarrier")
    public void exportCarrier(@RequestParam(required = false) String createDateStart, @RequestParam(required = false) String createDateEnd, HttpServletResponse response) throws Exception {
        GhOrderAnalysisQueryDto params = new GhOrderAnalysisQueryDto();
        if (StringUtils.hasText(createDateStart)) {
            params.setCreateTimeStart(LocalDate.parse(createDateStart, dateTimeFormatter));
        }
        if (StringUtils.hasText(createDateEnd)) {
            params.setCreateTimeEnd(LocalDate.parse(createDateEnd, dateTimeFormatter));
        }
        List<OrderCountDto> orderCountDtos = orderAnalysisService.carrierOrderQuery(params);
        List<LinePriceDataDto> linePriceDataDtos = orderAnalysisService.carrierPriceQuery(params);
        InputStream is = new ClassPathResource("templates/业务量汇总.xlsx").getInputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("供应商业务量汇总", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        ExcelWriter write = EasyExcel.write(outputStream).withTemplate(is).build();
        WriteSheet sheet1 = EasyExcel.writerSheet(0, "业务量对比").build();
        ;
        WriteSheet sheet2 = EasyExcel.writerSheet(1, "线路对比").build();
        ;
        write.fill(orderCountDtos, sheet1);
        write.fill(linePriceDataDtos, sheet2);
        // 关闭流
        write.finish();
    }
}
